package com.itcase.JDBC_02.dao;

import com.itcase.JDBC_02.JDBCUtils.JDBCUtil;
import com.itcase.JDBC_02.domin.student;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;

public class studentDaoImpl implements studentDao {
    //查询所有学生信息
    @Override
    public ArrayList<student> findAll() {
        ArrayList<student> list = new ArrayList<>();
        Connection con = null;
        Statement stat = null;
        ResultSet rs = null;

        try {
           // con = DriverManager.getConnection("jdbc:mysql://localhost/db2", "root", "root");
            con = JDBCUtil.getConnection();
            stat = con.createStatement();
            String s = "SELECT * FROM student";
            rs = stat.executeQuery(s);
            while (rs.next()) {
                Integer id = rs.getInt("id");
                String name = rs.getString("name");
                Integer age = rs.getInt("age");
                Date birthday = rs.getDate("birthday");

                student st = new student(id, name, age, birthday);

                list.add(st);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(con,stat,rs);
        }
        return list;
    }

    //条件查询
    @Override
    public student findById(Integer id) {
        student st = new student();
        Connection con = null;
        Statement stat = null;
        ResultSet rs = null;
        try {
           // Class.forName("com.mysql.jdbc.Driver");
            //con = DriverManager.getConnection("jdbc:mysql://localhost/db2", "root", "root");
            con = JDBCUtil.getConnection();
            stat = con.createStatement();
            String s = "Select * FROM student WHERE id='"+id+"'";
            rs = stat.executeQuery(s);
            while (rs.next()) {
                Integer sid = rs.getInt("id");
                String name = rs.getString("name");
                Integer age = rs.getInt("age");
                Date birthday = rs.getDate("birthday");
                st.setSid(sid);
                st.setName(name);
                st.setAge(age);
                st.setBirthday(birthday);

            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(con,stat,rs);
        }
        return st;
    }

    //添加学生信息
    @Override
    public int insert(student stu) {
        Connection con = null;
        Statement stat = null;
        int result = 0;
        try {
          //  Class.forName("com.mysql.jdbc.Driver");
            //con = DriverManager.getConnection("jdbc:mysql://localhost/db2", "root", "root");
            con = JDBCUtil.getConnection();
            stat = con.createStatement();

            java.util.Date d = stu.getBirthday();
            SimpleDateFormat sim = new SimpleDateFormat("yyyy-MM-dd");
            String birthday = sim.format(d);
            String sql = "INSERT INTO student VALUES('" + stu.getSid() + "','" + stu.getName() + "','" + stu.getAge() + "','" + birthday + "')";
            result = stat.executeUpdate(sql);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.close(con,stat);
        }
        return result;
    }
//修改学生信息
    @Override
    public int update(student stu) {
        Connection con = null;
        Statement stat = null;
        int result = 0;
        try {
            //Class.forName("com.mysql.jdbc.Driver");
            //con = DriverManager.getConnection("jdbc:mysql://localhost/db2", "root", "root");
            con = JDBCUtil.getConnection();
            stat = con.createStatement();

            Date d = (Date) stu.getBirthday();
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            String birthday = sdf.format(d);
            String sql = "UPDATE student SET id='"+stu.getSid()+"',name='"+stu.getName()+"',age='"+stu.getAge()+"',birthday='"+birthday+"' WHERE id='"+stu.getSid()+"'";
            result = stat.executeUpdate(sql);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.close(con,stat);
        }
        return result;
    }
//删除学生信息
    @Override
    public int delete(Integer id) {
        Connection con = null;
        Statement stat = null;
        int result = 0;

        try {
            //Class.forName("com.mysql.jdbc.Driver");
           // con =  DriverManager.getConnection("jdbc:mysql://localhost/db2", "root", "root");
            con = JDBCUtil.getConnection();
            stat = con.createStatement();
            String sql = "DELETE FROM student WHERE id='"+id+"'";
            result = stat.executeUpdate(sql);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.close(con,stat);
        }
        return result;
    }
}
